package com.hqq.persistence_tier.dao;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Optional;
import java.util.Spliterator;
import java.util.Spliterators;
import java.util.function.Consumer;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

/**
 * DBCustomerDao
 * {@link CustomerDao}的实现 用于将数据持久化在RDBMS中
 * Created by heqianqian on 2017/8/9.
 */
public class DBCustomerDao implements CustomerDao {

    private DataSource dataSource;

    public DBCustomerDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Stream<Customer> getAll() throws Exception {
        Connection connection;
        try {
            connection = getConnection();
            PreparedStatement psmt = connection.prepareStatement("SELECT * FROM CUSTOMERS");
            ResultSet resultSet = psmt.executeQuery();
            return StreamSupport.stream(new Spliterators.AbstractSpliterator<Customer>(Long.MAX_VALUE, Spliterator.ORDERED) {
                @Override
                public boolean tryAdvance(Consumer<? super Customer> action) {
                    try {
                        if (!resultSet.next()) {
                            return false;
                        }
                        action.accept(createCustomer(resultSet));
                        return true;
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                }
            }, false).onClose(() -> mutedClose(connection));
        } catch (SQLException e) {
            throw new Exception(e);
        }
    }

    @Override
    public Optional<Customer> getById(int id) throws Exception {
        Connection connection;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM CUSTOMERS WHERE ID=?");
            preparedStatement.setInt(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return Optional.of(createCustomer(resultSet));
            } else {
                return Optional.empty();
            }
        } catch (SQLException e) {
            throw new Exception(e);
        }
    }

    @Override
    public boolean add(Customer customer) throws Exception {
        if (getById(customer.getId()).isPresent()) {//当前ID的用户已经存在
            return false;
        }
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO CUSTOMERS VALUES (?,?,?)");) {
            preparedStatement.setInt(1, customer.getId());
            preparedStatement.setString(2, customer.getFirstName());
            preparedStatement.setString(3, customer.getLastName());
            return preparedStatement.execute();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public boolean update(Customer customer) throws Exception {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("UPDATE CUSTOMERS SET FNAME=?,LNAME=? WHERE ID=?");) {
            preparedStatement.setString(1, customer.getFirstName());
            preparedStatement.setString(2, customer.getLastName());
            preparedStatement.setInt(3, customer.getId());
            return preparedStatement.executeUpdate() > 0;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public boolean delete(Customer customer) throws Exception {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM CUSTOMERS WHERE ID=?");) {
            preparedStatement.setInt(1, customer.getId());
            return preparedStatement.executeUpdate() > 0;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    private void mutedClose(Connection connection) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Customer createCustomer(ResultSet resultSet) throws SQLException {
        return new Customer(resultSet.getInt("ID"),
                resultSet.getString("FNAME"),
                resultSet.getString("LNAME"));
    }
}
